package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import model.Product;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

@Service("productDAO")
public class ProductDAO implements IProductDAO{
	 public static final String INSERT =
	            "INSERT INTO PRODUCTS " +
	                    "(NAME,PRICE,DESCRIPTION,M_ID) " +
	                    "VALUES(?,?,?,?)";
	    public static final String UPDATE =
	            "UPDATE PRODUCTS " +
	                    "SET NAME=?,PRICE=?,DESCRIPTION=? , M_ID=? " +
	                    "WHERE ID=?";
	    public static final String DELETE =
	            "DELETE FROM PRODUCTS " +
	                    "WHERE ID=?";
	    
	    public static final String READ_ALL_PRODUCTS_BY_U_ID =
	            "SELECT * " +
	            "From PRODUCTS,MANUFACTURERS,USERS " +
	            "where USERS.ID=? " +
	            "AND USERS.ID=MANUFACTURERS.U_ID " +
	            "AND PRODUCTS.M_ID=MANUFACTURERS.ID ";
	    public static final String READ_ALL_PRODUCTS_BY_SOMECONDITON =
	            "SELECT * " +
	            "From PRODUCTS " +
	            "where M_ID=? " +
	            "AND NAME LIKE '?%' " +
	            "AND PRICE BETWEEN ? AND ?  ";
	    
	    public static final String READ_ALL_PRODUCTS_BY_PRICE =
	            "SELECT * " +
	            "From PRODUCTS " +
	            "where PRICE BETWEEN ? AND ? ";
   
	    public static final String READ_BY_ID =
	    			"SELECT * " +
                    "FROM PRODUCTS " +
                    "WHERE ID = ?";
	    
	    public static final String DELETE_BY_M_ID=
	    	"DELETE FROM PRODUCTS " +
	    	"WHERE M_ID=? ";
	    
	    public static final String READ_PRODUCT_BY_M_ID=
	    	"SELECT * FROM PRODUCTS " +
	    	"WHERE M_ID=? ";
	    
	    public static final String READ_PRODUCT_BY_NAME=
	    	"SELECT * FROM PRODUCTS " +
	    	"WHERE NAME LIKE ? ";
	    
	    public static final String READ_ALL_PRODUCTS=
	    	"SELECT * " +
	    	"FROM PRODUCTS ";
	    
	    public static final String READ_ALL_NAME_PRODUCTS=
		    	"SELECT DISTINCT NAME " +
		    	"FROM PRODUCTS ";
	    
	    @Resource
	    private JdbcTemplate jdbcTemplate;

	    public void addProduct(final String name, final double price, final String description,final String m_id) {
	        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection)throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS);
	                pst.setString(1, name);
	                pst.setDouble(2, price);
	                pst.setString(3, description);
	                pst.setString(4, m_id);
	                return pst;
	            }
	        };
	        KeyHolder keyHolder = new GeneratedKeyHolder();
	        jdbcTemplate.update(preparedStatementCreator, keyHolder);
	    }

	    public Product getOneProduct(final int id) {
	        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(READ_BY_ID);
	                pst.setInt(1, id);
	                return pst;
	            }
	        };
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                		resultSet.getString("id"),
	                		resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                        
	                );
	            }
	        });
	        return result.get(0);
	    }

	   public void deleteProduct(final String id){
		   PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(DELETE);
	                pst.setString(1, id);
	                return pst;
	            }
	        };
	        jdbcTemplate.update(preparedStatementCreator);
	   }
	   
	   public void deleteProductByMID(final int id){
		   PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(DELETE_BY_M_ID);
	                pst.setInt(1, id);
	                return pst;
	            }
	        };
	        jdbcTemplate.update(preparedStatementCreator);
	   }
	   
	   public void updateProduct(
			   final String name,
			   final double price,
			   final String desc,
			   final int id,
			   final String m_id
			   ){
		   PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(UPDATE);
	                pst.setString(1, name);
	                pst.setDouble(2, price);
	                pst.setString(3, desc);
	                pst.setString(4, m_id);
	                pst.setInt(5, id);
	                return pst;
	            }
	        };
	        jdbcTemplate.update(preparedStatementCreator);
	   }
	   
	    public List<Product> getAllProductBy_U_ID(final int u_id) {
	    	  PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement pst = connection.prepareStatement(READ_ALL_PRODUCTS_BY_U_ID);
		                pst.setInt(1, u_id);
		                return pst;
		            }
		        };
	    	
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }
	    
	    public List<Product> getAllProductByPrice(final double startPrice, final double endPrice) {
	    	  PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement pst = connection.prepareStatement(READ_ALL_PRODUCTS_BY_PRICE);
		                pst.setDouble(1, startPrice);
		                pst.setDouble(2, endPrice);
		                return pst;
		            }
		        };
	    	
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }
	    
	    public List<Product> getAllProductByName(final String name) {
	    	  PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement pst = connection.prepareStatement(READ_PRODUCT_BY_NAME);
		                pst.setString(1, name+"%");
		                return pst;
		            }
		        };
	    	
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }
	    
	    public List<Product> getAllProductBy_M_ID(final int m_id) {
	    	  PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement pst = connection.prepareStatement(READ_PRODUCT_BY_M_ID);
		                pst.setInt(1, m_id);
		                return pst;
		            }
		        };
	    	
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }
	    
	    public List<Product> getAllProductByCondition(final int m_id, final String productName, final int startPrice, final int endPrice) {
	    	  PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement pst = connection.prepareStatement(READ_ALL_PRODUCTS_BY_SOMECONDITON);
		                pst.setInt(1, m_id);
		                pst.setString(2, productName);
		                pst.setInt(3, startPrice);
		                pst.setInt(4, endPrice);
		                return pst;
		            }
		        };
	    	
	        List<Product> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }
	    
	    public List<Product> getAllProduct() {
	    	  
	        List<Product> result = jdbcTemplate.query(READ_ALL_PRODUCTS, new RowMapper<Product>() {
	            @Override
	            public Product mapRow(ResultSet resultSet, int i) throws SQLException {
	                return new Product(
	                        resultSet.getString("id"),
	                        resultSet.getString("name"),
	                        resultSet.getDouble("price"),
	                        resultSet.getString("description"),
	                        resultSet.getString("m_id")
	                );
	            }
	        });
	        if (result.isEmpty()){
	        	return null;
	        }
	        return result;
	    }

	    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	        this.jdbcTemplate = jdbcTemplate;
	    }

		@Override
		public List<String> getNameProductMatched(String query) {
			PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	                PreparedStatement pst = connection.prepareStatement(READ_ALL_NAME_PRODUCTS);
	                return pst;
	            }
	        };
    	
        List<String> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getString("name");           
            }
        });
        List<String> matched = new ArrayList<String>();
		String strTemp = null;
		query = query.toLowerCase();
		for(int i=0; i < result.size(); i++) {
            strTemp = result.get(i).toLowerCase();
            if(strTemp.contains(query)) {
                matched.add(result.get(i));
            }
        }
        if (matched.isEmpty()){
        	return null;
        }
			return matched;
		}
}
